/****** Object:  StoredProcedure [dbo].[factFraudEvent]    Script Date: 4.5.2021 09:29:55 ******/
--Insert Fraud staging table names to LSInsight$SourceTables table
EXEC dbo.[LSInsight$InsertSourceTable] @SourceTable = 'Fraud Event';
GO
EXEC dbo.[LSInsight$InsertSourceTable] @SourceTable = 'Loss Prevention Trigger';
GO

--Table name fix from [FactFraudEvent] to [fFraudEvent]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DW].[FactFraudEvent]') AND type in (N'U'))
DROP TABLE [DW].[FactFraudEvent]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DW].[fFraudEvent]') AND type in (N'U'))
CREATE TABLE [DW].[fFraudEvent](
	[SK_Company] [int] NULL,
	[LineNo] [int] NULL,
	[TransactionNo] [nvarchar](100) NULL,
	[RowID] [bigint] NULL,
	[Description] [nvarchar](100) NULL,
	[Incident Indentification Type] [nvarchar](100) NULL,
	[Incident Threshold Type] [nvarchar](100) NULL,
	[Receipt No_] [nvarchar](20) NULL,
	[SK_Staff] [int] NOT NULL,
	[SK_Location] [int] NOT NULL,
	[SK_POSTerminal] [int] NOT NULL,
	[Posting Date] [date] NULL,
	[Time] [datetime] NULL,
	[Trans_ Value] [decimal](38, 20) NULL
) ON [PRIMARY]

GO


-- =============================================
-- Author:		Kristinn Thor Sigurjonsson
-- Create date: 2021-03-05
-- Modified date: 2021-04-14 Use Merge to eliminate risk of duplicate rows
--				  2021-05-04 Added ranking to select latest update from staging
-- Description:	SP with option of Incremental load for FactFraudEvent
-- =============================================
CREATE PROCEDURE [dbo].[factFraudEvent] 

	@FraudEventRowID bigint, 
	@FraudEventNewRowID bigint  

	
AS
BEGIN
SET NOCOUNT ON;


WITH
/*
Get the list of companies used
*/
tCompanies
AS
(SELECT
		dCOM.[SK_Company] AS [Company]
	   ,dCOM.[CompanyPrefix]
	FROM [DW].[dCompany] dCOM
	WHERE dCOM.[SK_Company] <> -1),
/*
Set up dimensions and support tables used with the main data process
*/
tLocation
AS
(SELECT
		dLOC.[SK_location]
	   ,tCOM.[CompanyPrefix]
	   ,dLOC.[LocationCode]
	FROM DW.[dLocation] dLOC
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dLOC.[Company] = tCOM.[Company]),

tPOSTerminal
AS
(SELECT
		dPOT.[SK_POSTerminal]
	   ,dPOT.[No]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dPOSTerminal] dPOT
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dPOT.[Company] = tCOM.[Company]),

tStaff
AS
(SELECT
		dSTA.[SK_Staff]
	   ,dSTA.[ID]
	   ,dSTA.[SourceTable]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dStaff] dSTA
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dSTA.[Company] = tCOM.[Company]),

tStore
AS
(SELECT
		dSTO.[SK_Store]
	   ,dSTO.[StoreNo]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dStore] dSTO
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dSTO.[Company] = tCOM.[Company])

/*
Get Values from [dbo].[stg$Loss Prevention Trigger] to add Trigger descriptions to Reports 
- might evolve to a dimension later
*/

,
tFroudTriggerDescription
AS
(SELECT
		[CompanyPrefix]
	   ,MAX([bigint_timestamp]) AS MaxRowID
	   ,[No_]
	   ,[Description]
	   ,CASE [Incident Indentification Type]
			WHEN 0 THEN 'Staff'
			WHEN 1 THEN 'Store'
			WHEN 2 THEN 'POS Terminal'
			ELSE 'Type missing'
		END AS [Incident Indentification Type]
	   ,CASE [Incident Threshold Type]
			WHEN 0 THEN 'Value'
			WHEN 1 THEN 'Quantity'
			WHEN 2 THEN 'Over Average Normalized Value'
			WHEN 3 THEN 'Over Average Normalized Quantity'
			WHEN 4 THEN 'Normalized Value'
			WHEN 5 THEN 'Normalized Quantity'
			ELSE 'Type missing'
		END AS [Incident Threshold Type]
	FROM [dbo].[stg$Loss Prevention Trigger]
	GROUP BY [CompanyPrefix]
			,[No_]
			,[Description]
			,[Incident Indentification Type]
			,[Incident Threshold Type])
/*
Select Fraud events and join with dimensions
*/
,tFraudEvents AS (
SELECT
	tCOM.[Company] AS [SK_Company]
   ,sFRE.[Line No_] AS [LineNo]
   ,CAST(sFRE.[Transaction No_] AS NVARCHAR(100)) AS [TransactionNo]
   ,[bigint_timestamp] AS [RowID]
   ,tFTD.[Description]
   ,tFTD.[Incident Indentification Type]
   ,tFTD.[Incident Threshold Type]
   ,[Receipt No_]
   ,ISNULL(tSTA.[SK_Staff], -1) AS [SK_Staff]
   ,ISNULL(tLOC.[SK_Location], -1) AS [SK_Location]
   ,ISNULL(tPOT.[SK_POSTerminal], -1) AS [SK_POSTerminal]
   ,CAST(sFRE.[Trans_ Date] AS DATE) AS [Posting Date]
   ,CAST(DATEADD(mi, DATEDIFF(mi, 0, sFRE.[Trans_ Time]), 0) AS DATETIME) AS [Time]
   ,[Trans_ Value]
FROM [dbo].[stg$Fraud Event] sFRE
LEFT OUTER JOIN [tStore] tSTO
	ON sFRE.[CompanyPrefix] = tSTO.[CompanyPrefix]
		AND sFRE.[Store No_] = tSTO.[StoreNo]
LEFT OUTER JOIN tFroudTriggerDescription tFTD
	ON sFRE.[CompanyPrefix] = tFTD.[CompanyPrefix]
		AND sFRE.[Trigger No_] = tFTD.[No_]
LEFT OUTER JOIN tCompanies tCOM
	ON sFRE.[CompanyPrefix] = tCOM.[CompanyPrefix]
LEFT OUTER JOIN [tStaff] tSTA --ST
	ON sFRE.[CompanyPrefix] = tSTA.[CompanyPrefix]
		AND sFRE.[Staff No_] = tSTA.[ID]
		AND tSTA.[SourceTable] = 'Staff'
LEFT OUTER JOIN [tLocation] tLOC
	ON sFRE.[CompanyPrefix] = tLOC.[CompanyPrefix]
		AND sFRE.[Store No_] = tLOC.[LocationCode]
LEFT OUTER JOIN [tPOSTerminal] tPOT
	ON sFRE.[CompanyPrefix] = tPOT.[CompanyPrefix]
		AND sFRE.[POS Terminal No_] = tPOT.[No]

WHERE (sFRE.[bigint_timestamp] > @FraudEventRowID
	AND sFRE.[bigint_timestamp] <= @FraudEventNewRowID))

,tFraudEventsRanked
AS
(SELECT
		*
	   ,DENSE_RANK() OVER (PARTITION BY [SK_Company], [TransactionNo], [LineNo], [Receipt No_], [Posting Date]  ORDER BY [RowID] DESC) AS [Rank]
	FROM tFraudEvents)

 MERGE [DW].[fFraudEvent] AS Target USING 
	(
 Select 
			[SK_Company]
           ,[LineNo]
           ,[TransactionNo]
           ,[RowID]
           ,[Description]
           ,[Incident Indentification Type]
           ,[Incident Threshold Type]
           ,[Receipt No_]
           ,[SK_Staff]
           ,[SK_Location]
           ,[SK_POSTerminal]
           ,[Posting Date]
           ,[Time]
           ,[Trans_ Value]
FROM tFraudEventsRanked
WHERE [Rank] = 1) AS Source
	ON
	Target.[SK_Company] = Source.[SK_Company]
	AND Target.[LineNo] = Source.[LineNo]
	AND Target.[TransactionNo] = Source.[TransactionNo]
	AND Target.[Receipt No_] = Source.[Receipt No_]
	AND Target.[Posting Date] = Source.[Posting Date]

	WHEN MATCHED
	THEN UPDATE
		SET [RowID] = Source.[RowID]
		,[Description] = Source.[Description]
		,[Incident Indentification Type] = Source.[Incident Indentification Type]
		,[Incident Threshold Type] = Source.[Incident Threshold Type]
		,[SK_Staff] = Source.[SK_Staff]
		,[SK_Location] = Source.[SK_Location]
		,[SK_POSTerminal] = Source.[SK_POSTerminal]
		,[Time] = Source.[Time]
		,[Trans_ Value] = Source.[Trans_ Value]

	WHEN NOT MATCHED BY TARGET
	THEN INSERT
	([SK_Company]
           ,[LineNo]
           ,[TransactionNo]
           ,[RowID]
           ,[Description]
           ,[Incident Indentification Type]
           ,[Incident Threshold Type]
           ,[Receipt No_]
           ,[SK_Staff]
           ,[SK_Location]
           ,[SK_POSTerminal]
           ,[Posting Date]
           ,[Time]
           ,[Trans_ Value])
     VALUES
           (Source.[SK_Company]
           ,Source.[LineNo]
           ,Source.[TransactionNo]
           ,Source.[RowID]
           ,Source.[Description]
           ,Source.[Incident Indentification Type]
           ,Source.[Incident Threshold Type]
           ,Source.[Receipt No_]
           ,Source.[SK_Staff]
           ,Source.[SK_Location]
           ,Source.[SK_POSTerminal]
           ,Source.[Posting Date]
           ,Source.[Time]
           ,Source.[Trans_ Value])
	;
SELECT
	'RowCount' = @@rowcount

END